package com.src.xyzk_personal.EOL;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.src.xyzk_personal.EolNewFunctionActivity;
import com.src.xyzk_personal.Service.TApplication;
import com.src.xyzk_personal.config.Common;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbAdapter {
    private static final String TAG = "DbAdapter";
    private static final int db_version = 1;

    private DbOpenHelper dbhelper = null;
    private SQLiteDatabase m_db = null;
    private Context m_context = null;

    //构造方法
    public DbAdapter(Context context) {
        m_context = context;
        dbhelper = new DbOpenHelper(context);
    }

    public DbAdapter() {
        dbhelper = new DbOpenHelper();
    }

    public void close() {
        if (m_db != null) m_db.close();
    }

    public void open() throws SQLiteException {
        try {
            m_db = dbhelper.getWritableDatabase();
            if (Common.Debug) Log.i(TAG, "Write And Read database!");
        } catch (SQLiteException ex) {
            m_db = dbhelper.getReadableDatabase();
            if (Common.Debug) Log.e(TAG, "only read database!");
        }
    }

    public void StartAddMost()    //开始大批量事务
    {
        String create_sql = "begin transaction";
        m_db.execSQL(create_sql);
    }

    public void StopAddMost()        //结束大批量事务
    {
        String create_sql = "commit transaction";
        m_db.execSQL(create_sql);
    }

    //更新主表
    public void addmainTable(ContentValues value, boolean update) {
        if (update)
            value.put(TableMain.update, "已上传");
        else
            value.put(TableMain.update, "未上传");
        if (m_db != null)
            m_db.insert(TableMain.Name, null, value);
    }

    //更新是否上传标记
    public boolean UpdataTableUpdate(String table, String Pvin, int Ptestnum, int update) {
        if (m_db == null) return false;
        ContentValues cv = new ContentValues();
        cv.put(TableMain.update, update + "");
        cv.put(TableMain.station, "G5");
        m_db.update(table, cv, TableMain.vin + " like ? and " + TableMain.testnum + "=?", new String[]{Pvin, "" + Ptestnum});
        //	m_db.update(table, values, whereClause, whereArgs)
        return true;
    }

    /**
     * 根据主表数据查询子表的数据
     * service层需要
     */
    public List querysubdataforvin(String vin, int Testnum) {
        try {
            if (m_db == null) return null;
            String querysubdataforvin = "select " +
                    TableData.ID + "," +
                    TableData.testcode + "," +
                    TableData.context + "," +
                    TableData.testdata + "," +
                    TableData.testctand + "," +
                    TableData.result + "," +
                    TableData.testnum
                    + " from " + vin + " where " + TableData.testnum + "=" + Testnum;
            Cursor cursor = m_db.rawQuery(querysubdataforvin, null);
            List querysubdatalist = new ArrayList<>();
            while (cursor != null && cursor.moveToNext()) {
                HashMap map = new HashMap<>();
                map.put(TableData.ID, cursor.getString(cursor.getColumnIndex(TableData.ID)));
                map.put(TableData.testcode, cursor.getString(cursor.getColumnIndex(TableData.testcode)));
                map.put(TableData.context, cursor.getString(cursor.getColumnIndex(TableData.context)));
                map.put(TableData.testdata, cursor.getString(cursor.getColumnIndex(TableData.testdata)));
                map.put(TableData.testctand, cursor.getString(cursor.getColumnIndex(TableData.testctand)));
                map.put(TableData.result, cursor.getString(cursor.getColumnIndex(TableData.result)));
                querysubdatalist.add(map);
            }
            return querysubdatalist;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 修改数据库的上传状态
     * service补传数据时使用
     */
    public boolean updatemaintable(int id) {
        try {
            if (m_db == null) return false;
            String updatemainSql = "update " + TableMain.Name + " set " + TableMain.update + " = '已上传'  where " + TableMain.ID + " = " + id;
            m_db.execSQL(updatemainSql);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public List queryMainTableUnupdate() {
        try {
            if (m_db == null) return null;
            String querymainSql = "select " +
                    TableMain.ID + "," +
                    TableMain.vin + "," +
                    TableMain.car + "," +
                    TableMain.cartype + "," +
                    TableMain.carname + "," +
                    TableMain.station + "," +
                    TableMain.esk + "," +
                    TableMain.pin + "," +
                    TableMain.printdata + "," +
                    TableMain.device + "," +
                    TableMain.testnum + "," +
                    TableMain.timestart + "," +
                    TableMain.timeend + "," +
                    TableMain.logfileName + "," +
                    TableMain.logpath + "," +
                    TableMain.result + "," +
                    TableMain.appversion + "," +
                    TableMain.carcolor + "," +
                    TableMain.update
                    + " from " + TableMain.Name + " where " +
                    TableMain.update + "='未上传'";
            Cursor cursor = m_db.rawQuery(querymainSql, null);
            List querymainlist = new ArrayList();
            while (cursor != null && cursor.moveToNext()) {
                HashMap map = new HashMap<>();
                map.put(TableMain.ID, cursor.getString(cursor.getColumnIndex(TableMain.ID)));
                map.put(TableMain.vin, cursor.getString(cursor.getColumnIndex(TableMain.vin)));
                map.put(TableMain.car, cursor.getString(cursor.getColumnIndex(TableMain.car)));
                map.put(TableMain.cartype, cursor.getString(cursor.getColumnIndex(TableMain.cartype)));
                map.put(TableMain.carname, cursor.getString(cursor.getColumnIndex(TableMain.carname)));
                map.put(TableMain.station, cursor.getString(cursor.getColumnIndex(TableMain.station)));
                map.put(TableMain.esk, cursor.getString(cursor.getColumnIndex(TableMain.esk)));
                map.put(TableMain.pin, cursor.getString(cursor.getColumnIndex(TableMain.pin)));
                map.put(TableMain.printdata, cursor.getString(cursor.getColumnIndex(TableMain.printdata)));
                map.put(TableMain.device, cursor.getString(cursor.getColumnIndex(TableMain.device)));
                map.put(TableMain.testnum, cursor.getString(cursor.getColumnIndex(TableMain.testnum)));
                map.put(TableMain.timestart, cursor.getString(cursor.getColumnIndex(TableMain.timestart)));
                map.put(TableMain.timeend, cursor.getString(cursor.getColumnIndex(TableMain.timeend)));
                map.put(TableMain.logfileName, cursor.getString(cursor.getColumnIndex(TableMain.logfileName)));
                map.put(TableMain.logpath, cursor.getString(cursor.getColumnIndex(TableMain.logpath)));
                map.put(TableMain.result, cursor.getString(cursor.getColumnIndex(TableMain.result)));
                map.put(TableMain.appversion, cursor.getString(cursor.getColumnIndex(TableMain.appversion)));
                map.put(TableMain.carcolor, cursor.getString(cursor.getColumnIndex(TableMain.carcolor)));
                querymainlist.add(map);
            }
            return querymainlist;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //返回测试次数,负数失败
    public int CreateTableData(String tbname) {
        int testnum = 0;
        if (m_db == null) {
            if (Common.Debug) Log.e(TAG, "Database not open!");
            return -1;
        }
        //创建主表
        String create_sql = "CREATE TABLE IF NOT EXISTS " +
                tbname + "(" +
                TableData.ID + " integer primary key autoincrement," +
                TableData.testnum + " integer," +
                TableData.testcode + " integer," +
                TableData.context + " varchar(50)," +
                TableData.testdata + " varchar(50)," +
                TableData.testctand + " varchar(50)," +
                TableData.result + " varchar(10)," +
                TableData.update + " integer" + ")";
        m_db.execSQL(create_sql);
        //先查询
        Cursor csr = null;
        csr = m_db.rawQuery("select * from " + tbname, null);
        if (csr.moveToLast())
            testnum = csr.getInt(csr.getColumnIndex(TableData.testnum));
        if (csr != null) csr.close();
        testnum++;
        return testnum;
    }

    public void addDatatotable(String tbname, ContentValues value, boolean update) {
        if (update)
            value.put(TableData.update, 1);
        else
            value.put(TableData.update, 0);
        if (m_db != null)
            m_db.insert(tbname, null, value);
    }

    //查询详细数据表
    public List<Map<String, String>> queryDataTable(String table, String where, String[] whereArgs,
                                                    String orderBy, String limit) {
        try {
            if (m_db == null) {
                if (Common.Debug) Log.e(TAG, "Database not open!");
                return null;
            }
            Cursor csr = null;
            csr = m_db.query(table, null, where, whereArgs,
                    null, null, orderBy, limit);
            List<Map<String, String>> lst = new ArrayList<Map<String, String>>();
            while (csr != null && csr.moveToNext()) {
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("SHOW_ID", csr.getString(csr.getColumnIndex(TableData.testcode)));
                map.put("SHOW_CONTEXT", csr.getString(csr.getColumnIndex(TableData.context)));
                String data = csr.getString(csr.getColumnIndex(TableData.testdata));
                //if(data.length() > 0)
                for (int s = 0; s < data.length(); s++) {
                    byte[] databyte = data.getBytes();
                    if (databyte[s] == 0)
                        databyte[s] = '-';
                    data = new String(databyte);
                }
                map.put("SHOW_RESULT", data);
                map.put("SHOW_TIME", csr.getString(csr.getColumnIndex(TableData.result)));
                lst.add(map);
            }
            if (csr != null) csr.close();
            return lst;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    //查询主表
    public List<Map<String, Object>> querymainTable(String where, String[] whereArgs,
                                                    String orderBy, String limit) {
        try {
            if (m_db == null) {
                if (Common.Debug) Log.e(TAG, "Database not open!");
                return null;
            }
            Cursor csr = null;
            csr = m_db.query(TableMain.Name, null, where, whereArgs,
                    null, null, orderBy, limit);
            List<Map<String, Object>> lst = new ArrayList<Map<String, Object>>();
            while (csr != null && csr.moveToNext()) {
                HashMap<String, Object> map = new HashMap<String, Object>();
                map.put(TableMain.vin, csr.getString(csr.getColumnIndex(TableMain.vin)));
                map.put(TableMain.cartype, csr.getString(csr.getColumnIndex(TableMain.cartype)));
                map.put(TableMain.station, csr.getString(csr.getColumnIndex(TableMain.station)));
                map.put(TableMain.timestart, csr.getString(csr.getColumnIndex(TableMain.timestart)));
                map.put(TableMain.timeend, csr.getString(csr.getColumnIndex(TableMain.timeend)));
                map.put(TableMain.result, csr.getString(csr.getColumnIndex(TableMain.result)));
                map.put(TableMain.printdata, csr.getString(csr.getColumnIndex(TableMain.printdata)));
                map.put(TableMain.device, csr.getString(csr.getColumnIndex(TableMain.device)));
                map.put(TableMain.appversion, csr.getString(csr.getColumnIndex(TableMain.appversion)));
                map.put(TableMain.testnum, csr.getInt(csr.getColumnIndex(TableMain.testnum)));
                map.put(TableMain.ID, csr.getInt(csr.getColumnIndex(TableMain.ID)));
                lst.add(map);
            }
            if (csr != null) csr.close();
            return lst;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public ArrayList<Map<String, String>> UpquerymainTable(String where, String[] whereArgs,
                                                           String orderBy, String limit) {
        try {
            if (m_db == null) {
                if (Common.Debug) Log.e(TAG, "Database not open!");
                return null;
            }
            int v_id = 0;
            Cursor csr = null;
            csr = m_db.query(TableMain.Name, null, where, whereArgs,
                    null, null, orderBy, limit);
            ArrayList<Map<String, String>> lst = new ArrayList<Map<String, String>>();
            while (csr != null && csr.moveToNext()) {
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("ID", "" + (v_id++));
                map.put("VIN", csr.getString(csr.getColumnIndex(TableMain.vin)));
                map.put("STATION", csr.getString(csr.getColumnIndex(TableMain.carname)));
                map.put("CAR", "S30");
                map.put("COLOR", csr.getString(csr.getColumnIndex(TableMain.carcolor)));
                map.put("TIME", csr.getString(csr.getColumnIndex(TableMain.timeend)));
                map.put("TESTNUM", csr.getString(csr.getColumnIndex(TableMain.testnum)));
                map.put("RESULT", csr.getString(csr.getColumnIndex(TableMain.result)));
                map.put("UP", csr.getString(csr.getColumnIndex(TableMain.update)));
                map.put("USER", csr.getString(csr.getColumnIndex(TableMain.appversion)));
                lst.add(map);
            }
            if (csr != null) csr.close();
            return lst;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    //查询主表,返回测试次数
    public int QueryMainTestNum(String vin) {
        if (m_db == null) {
            if (Common.Debug) Log.e(TAG, "Database not open!");
            return -1;
        }
        int testnum = 0;
        Cursor csr = null;
        csr = m_db.query(TableMain.Name, null, TableMain.vin + "=?", new String[]{vin},
                null, null, null, null);
        while (csr != null && csr.moveToNext()) {
            if (csr.getInt(csr.getColumnIndex(TableMain.testnum)) > testnum)
                testnum = csr.getInt(csr.getColumnIndex(TableMain.testnum));
        }
        if (csr != null) csr.close();
        return testnum;
    }

    private static class DbOpenHelper extends SQLiteOpenHelper {

        public DbOpenHelper(Context ctx) {
            super(ctx, Common.db_name, null, db_version);
            // TODO Auto-generated constructor stub
        }

        public DbOpenHelper() {
            super(TApplication.getContext(), Common.db_name, null, db_version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            //创建主表
            String create_sql = "CREATE TABLE " +
                    TableMain.Name + "(" +
                    TableMain.ID + " integer primary key autoincrement," +
                    TableMain.vin + " varchar(20)," +
                    TableMain.esk + " varchar(20)," +
                    TableMain.pin + " varchar(20)," +
                    TableMain.car + " varchar(20)," +
                    TableMain.cartype + " varchar(30)," +
                    TableMain.carcolor + " varchar(100)," +
                    TableMain.carname + " varchar(30)," +
                    TableMain.timestart + " varchar(20)," +
                    TableMain.timeend + " varchar(20)," +
                    TableMain.station + " varchar(20)," +
                    TableMain.appversion + " varchar(20)," +
                    TableMain.result + " varchar(10)," +
                    TableMain.testnum + " integer," +
                    TableMain.printdata + " varchar(200)," +
                    TableMain.device + " varchar(12)," +
                    TableMain.logfileName + " varchar(20)," +
                    TableMain.logpath + " varchar(20)," +
                    TableMain.update + " varchar(20)" + ")";
            db.execSQL(create_sql);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub

        }
    }

    public interface TableMain {
        String Name = "tablemain";  //表名
        String ID = "m_id";            //id
        String vin = "m_vin";        //vin码
        String esk = "m_esk";        //esk
        String pin = "m_pin";        //pin码
        String car = "m_car";        //车型
        String cartype = "m_cartype";    //车型代码
        String carcolor = "m_carcolor";    //car颜色
        String carname = "m_carname";    //carname
        String timestart = "m_timestart";    //开始时间
        String timeend = "m_timeend";        //结束时间
        String station = "m_station";        //测试工位
        String appversion = "m_appversion";        //手持终端版本号
        String result = "m_result";            //检测结果
        String testnum = "m_testnum";        //检测次数
        String printdata = "m_printdata";    //打印内容
        String device = "m_device";        //设备序列号
        String update = "m_update";        //是否上传
        String logfileName = "m_logfilename";
        String logpath = "m_logpath";
    }

    public interface TableData {
        String Name = "m_vin";    //以vin码作为存储名称
        String ID = "m_id";        //id
        String testnum = "m_testnum";    //测试序号,第几次
        String testcode = "m_testcode";    //统计号
        String context = "m_context";    //测试标题
        String testdata = "m_testdata";    //测试数据
        String testctand = "m_testctand"; //参考值
        String result = "m_result";        //单项检测结果
        String update = "m_update";    //是否上传
    }
}
